package com.infocollect.project.api.service;

import com.infocollect.common.utils.DateUtils;
import com.infocollect.common.utils.ServletUtils;
import com.infocollect.framework.config.InfoCollectConfig;
import com.infocollect.framework.security.ClientUser;
import com.infocollect.framework.security.LoginUser;
import com.infocollect.framework.security.service.TokenService;
import com.infocollect.project.api.enums.ApiTypeEnum;
import com.infocollect.project.api.helper.CurrentHelp;
import com.infocollect.project.api.helper.JSONUtil;
import com.infocollect.project.api.vo.ApiConfigVO;
import com.infocollect.project.api.vo.ApplyUserVO;
import com.infocollect.project.api.vo.AuditReportVo;
import com.infocollect.project.api.vo.CurrentUserInfoVO;
import com.infocollect.project.form.db.DBOperationHelper;
import com.infocollect.project.form.db.ExportExcel;
import com.infocollect.project.form.db.ValidateUtils;
import com.infocollect.project.form.vo.FormPageVO;
import com.infocollect.project.form.vo.HeadEntityVO;
import com.infocollect.project.form.vo.ParamBizFromVO;
import com.infocollect.project.form.vo.R;
import com.infocollect.project.grant.domain.QuestionAuditProcess;
import com.infocollect.project.grant.service.IQuestionAuditProcessService;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.time.LocalDate;
import java.util.*;

/**
 * 通用Api接口类
 */
@Service
public class ApiCommService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ITappApiConfigService apiConfigService;
    @Autowired
    private TokenService tokenService;
    @Autowired
    private IQuestionAuditProcessService auditProcessService;

    /**
     * 根据执行sql返回输出的字段名称
     *
     * @return
     */
    public R<List<String>> getApiFieldNames(JSONObject param) throws Exception {
        String sqlcontent = param.getString("sqlcontent");
        JSONObject dbParam = param.getJSONObject("paramjsonsrt");
        JSONObject requestparam = dbParam.getJSONObject("$query");
        JSONObject headparam =dbParam.getJSONObject("$header");
        JSONObject dbOperationParam =dbParam.getJSONObject("body");
//        CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
        LoginUser currentUserInfo = tokenService.getLoginUser(ServletUtils.getRequest());
        dbOperationParam.put("$user", currentUserInfo);
        dbOperationParam.put("$query", requestparam);
        dbOperationParam.put("$header", headparam);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, dbParam);
        return R.newOk(dbOperationHelper.getColumninfo(sqlcontent));
    }

    /**
     * 测试执行Api接口
     *
     * @param param
     * @return
     */
    public R<Object> testExecuteApi(JSONObject param) throws Exception {
        String sqlcontent = param.getString("sqlcontent");
        JSONObject dbParam = param.getJSONObject("paramjsonsrt");
        JSONObject requestparam = dbParam.getJSONObject("$query");
        JSONObject headparam =dbParam.getJSONObject("$header");
        JSONObject dbOperationParam =dbParam.getJSONObject("body");
//        CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
        LoginUser currentUserInfo = tokenService.getLoginUser(ServletUtils.getRequest());
        dbOperationParam.put("$user", currentUserInfo);
        dbOperationParam.put("$query", requestparam);
        dbOperationParam.put("$header", headparam);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, dbOperationParam);
        return R.newOk(dbOperationHelper.queryForList(sqlcontent));
    }

    /**
     * 导出xls
     *
     * @return 返回查询结果
     */
    public R<String> ExportExlce(JSONObject param, String ApiCode, HttpServletResponse response, HttpServletRequest req) throws Exception {
        ApiConfigVO apiconfig = apiConfigService.getApiConfigByCallMethodCode(ApiCode);
        if (apiconfig == null) {
            return R.newError("Api地址错误！");
        }
        if (apiconfig.getMethodType() != ApiTypeEnum.LIST) {
            return R.newError("导出接口只能支持列表类型Api接口！");
        }
        if (!StringUtils.isNotBlank(apiconfig.getHeadList()) || apiconfig.getHeadList().equals("[]")) {
            throw new Exception("未设置头部信息，导出失败！请联系管理员！");
        }
//        CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
        LoginUser currentUserInfo = tokenService.getLoginUser(ServletUtils.getRequest());
        param.put("$user", currentUserInfo);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, param);
        if (StringUtils.isNotBlank(apiconfig.getParamConfig())) {
            R<String> resultdata = dbOperationHelper.bizformdata(JSONUtil.jsonToObjList(ParamBizFromVO.class, apiconfig.getParamConfig()));
            if (resultdata.getCode() != 0) {
                return R.newError(resultdata.getMsg());
            }
        }
        List<HeadEntityVO> headEntityList = JSONUtil.jsonToObjList(HeadEntityVO.class, apiconfig.getHeadList());
        List<Map<String, Object>> datalist = dbOperationHelper.queryForList(apiconfig.getSqlContent());
        HSSFWorkbook workbook = ExportExcel.CreateExcel(apiconfig.getMethodName(), datalist, headEntityList);
//        response.setHeader("content-type", "application/octet-stream");
        LocalDate localDate = LocalDate.now();
        String excelFileName = ApiCode+"_"+localDate.toString() + ".xls";
        response.setHeader("fileName", excelFileName);
//        response.setContentType("application/octet-stream");
        try {
            FileOutputStream output= new  FileOutputStream( getAbsoluteFile(excelFileName) );
            workbook.write(output);
            output.flush();
            output.close();
//            response.flushBuffer();
//            workbook.write(response.getOutputStream());
            return R.newOk(excelFileName);
        } catch (Exception e) {
            e.printStackTrace();
            return R.newError(e.getMessage());
        }
    }
    /**
     * 导出xls空模版
     *
     * @return 返回查询结果
     */
    public R<String> ExportExlceTemplate(JSONObject param, String ApiCode, HttpServletResponse response, HttpServletRequest req) throws Exception {
        ApiConfigVO apiconfig = apiConfigService.getApiConfigByCallMethodCode(ApiCode);
        if (apiconfig == null) {
            return R.newError("Api地址错误！");
        }
        if (apiconfig.getMethodType() != ApiTypeEnum.LIST) {
            return R.newError("导出接口只能支持列表类型Api接口！");
        }
        if (!StringUtils.isNotBlank(apiconfig.getHeadList()) || apiconfig.getHeadList().equals("[]")) {
            throw new Exception("未设置头部信息，导出失败！请联系管理员！");
        }
//        CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
        LoginUser currentUserInfo = tokenService.getLoginUser(ServletUtils.getRequest());
        param.put("$user", currentUserInfo);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, param);
        if (StringUtils.isNotBlank(apiconfig.getParamConfig())) {
            R<String> resultdata = dbOperationHelper.bizformdata(JSONUtil.jsonToObjList(ParamBizFromVO.class, apiconfig.getParamConfig()));
            if (resultdata.getCode() != 0) {
                return R.newError(resultdata.getMsg());
            }
        }
        List<HeadEntityVO> headEntityList = JSONUtil.jsonToObjList(HeadEntityVO.class, apiconfig.getHeadList());
        List<Map<String, Object>> datalist = new ArrayList<>();
        HSSFWorkbook workbook = ExportExcel.CreateExcel(apiconfig.getMethodName(), datalist, headEntityList);
//        response.setHeader("content-type", "application/octet-stream");
        LocalDate localDate = LocalDate.now();
        String excelFileName = ApiCode+"_"+localDate.toString() + ".xls";
        response.setHeader("fileName", excelFileName);
//        response.setContentType("application/octet-stream");
        try {
            FileOutputStream output= new  FileOutputStream( getAbsoluteFile(excelFileName) );
            workbook.write(output);
            output.flush();
            output.close();
//            response.flushBuffer();
//            workbook.write(response.getOutputStream());
            return R.newOk(excelFileName);
        } catch (Exception e) {
            e.printStackTrace();
            return R.newError(e.getMessage());
        }
    }
    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public String getAbsoluteFile(String filename)
    {
        String downloadPath = InfoCollectConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }
    /**
     * 通用接口
     *
     * @return 返回查询结果
     */
    public R<Object> apiComm(JSONObject param, String ApiCode, HttpServletRequest req) throws Exception {
        R<Object> r = new R<>();
        ApiConfigVO apiconfig = apiConfigService.getApiConfigByCallMethodCode(ApiCode);
        if(apiconfig==null){
            return R.newError("请求接口地址错误！");
        }
        switch (apiconfig.getMethodType().getValue()) {
            case 1:
                r = apiQueryList(apiconfig, param, req);
                break;
            case 2:
                r = apiQuery(apiconfig, param, req);
                break;
            case 3:
                r = operateApi(apiconfig, param, req);
                break;
            case 5:
                r = queryForObject(apiconfig, param, req);
                break;
            default:
                r = R.newOk();
                break;
        }
        r.setCurDate(DateUtils.getTime());
        return r;
    }

    /**
     * 根据ApiCode 获取返回列表的头部信息
     * @param ApiCode
     * @return
     */
    public R<List<HeadEntityVO>> getListHeadCell(@PathVariable("ApiCode") String ApiCode){
        ApiConfigVO apiconfig = apiConfigService.getApiConfigByCallMethodCode(ApiCode);
        if (apiconfig == null) {
            return R.newError("Api地址错误！");
        }
        List<HeadEntityVO> headEntityList = new ArrayList<>();
        if (StringUtils.isNotBlank(apiconfig.getHeadList())) {
            headEntityList = JSONUtil.jsonToObjList(HeadEntityVO.class, apiconfig.getHeadList());
            for (int i = 0; i < headEntityList.size(); i++) {
                HeadEntityVO headEntity = headEntityList.get(i);
                headEntity.setId(i + "");
            }
        }
        return R.newOk(headEntityList);
    }
    /**
     * 通用查询列表接口
     *
     * @param param
     * @param
     * @return
     * @throws Exception
     */
    private R<Object> apiQueryList(ApiConfigVO apiconfig, JSONObject param, HttpServletRequest req) throws Exception {
        if (apiconfig == null) {
            return R.newError("Api地址错误！");
        }
        if("已审核".equals(param.get("auditflag")))
        {
            if (!"".equals(param.get("auditIdCode")) && param.get("auditIdCode") != null) {
                List<HeadEntityVO> headEntityList = new ArrayList<>();
                if (StringUtils.isNotBlank(apiconfig.getHeadList())) {
                    headEntityList = JSONUtil.jsonToObjList(HeadEntityVO.class, apiconfig.getHeadList());
                    for (int i = 0; i < headEntityList.size(); i++) {
                        HeadEntityVO headEntity = headEntityList.get(i);

                        if (headEntity.getHeadname().equals("证件号码") || headEntity.getHeadname().equals("身份证号")) {
                            param.remove("auditflag");
                            param.remove("auditbyname");
                            param.put(headEntity.getBindname(), param.get("auditIdCode"));
                            param.remove("auditIdCode");
                            break;
                        }
                    }
                }

            }
            else {
                apiconfig.setSqlContent(apiconfig.getSqlContent().replaceAll("maintable.auditflag =#\\u007Bauditflag}", "maintable.auditflag in ('审核通过','待确认','审核失败')"));
            }
        }else {
            if (!"".equals(param.get("auditIdCode")) && param.get("auditIdCode") != null) {
                List<HeadEntityVO> headEntityList = new ArrayList<>();
                if (StringUtils.isNotBlank(apiconfig.getHeadList())) {
                    headEntityList = JSONUtil.jsonToObjList(HeadEntityVO.class, apiconfig.getHeadList());
                    for (int i = 0; i < headEntityList.size(); i++) {
                        HeadEntityVO headEntity = headEntityList.get(i);

                        if (headEntity.getHeadname().equals("证件号码") || headEntity.getHeadname().equals("身份证号")) {
                            param.remove("auditflag");
                            param.remove("auditbyname");
                            param.put(headEntity.getBindname(), param.get("auditIdCode"));
                            param.remove("auditIdCode");
                            break;
                        }
                    }
                }

            }
        }
//        CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
        LoginUser currentUserInfo = tokenService.getLoginUser(ServletUtils.getRequest());
        param.put("$user", currentUserInfo);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, param);
        R<String> resultdata = bizformdata(req, param, dbOperationHelper, apiconfig);
        if (resultdata.getCode() != 0) {
            return R.newError(resultdata.getMsg());
        }
        List<HeadEntityVO> headEntityList = new ArrayList<>();
        if (StringUtils.isNotBlank(apiconfig.getHeadList())) {
            headEntityList = JSONUtil.jsonToObjList(HeadEntityVO.class, apiconfig.getHeadList());
            for (int i = 0; i < headEntityList.size(); i++) {
                HeadEntityVO headEntity = headEntityList.get(i);
                headEntity.setId(i + "");
            }
        }
        Map<String, Object> map = new HashMap();
        if (param.get("page") != null && param.get("page").toString() != "") {
            FormPageVO<Map<String, Object>> data = dbOperationHelper.queryPageForList(apiconfig.getSqlContent());
            map.put("showCelNames", apiconfig.getShowCelNames());
            map.put("columns", headEntityList);
            map.put("list", data.getRows());
            return R.newOk(map, data.getRecords());
        } else {
            List<Map<String, Object>> datalist = dbOperationHelper.queryForList(apiconfig.getSqlContent());
            map.put("columns", headEntityList);
            map.put("list", datalist);
            map.put("showCelNames", apiconfig.getShowCelNames());
            return R.newOk(map);
        }
    }

    /**
     * 通用查询接口
     *
     * @param param
     * @param
     * @return
     * @throws Exception
     */
    private R<Object> apiQuery(ApiConfigVO apiconfig, JSONObject param, HttpServletRequest req) throws Exception {
        if (apiconfig == null) {
            return R.newError("Api地址错误！");
        }
        //CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
        LoginUser loginUser = tokenService.getLoginUser(ServletUtils.getRequest());
        param.put("$user", loginUser);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, param);
        R<String> resultdata = bizformdata(req, param, dbOperationHelper, apiconfig);
        if (resultdata.getCode() != 0) {
            return R.newError(resultdata.getMsg());
        }
        return R.newOk(dbOperationHelper.queryForList(apiconfig.getSqlContent()));
    }
    /**
     * 通用查询实体对象
     *
     * @param param
     * @param
     * @return
     * @throws Exception
     */
    private R<Object> queryForObject(ApiConfigVO apiconfig, JSONObject param, HttpServletRequest req) throws Exception {
        if (apiconfig == null) {
            return R.newError("Api地址错误！");
        }
//        CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
        LoginUser currentUserInfo = tokenService.getLoginUser(ServletUtils.getRequest());
        param.put("$user", currentUserInfo);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, param);
        R<String> resultdata = bizformdata(req, param, dbOperationHelper, apiconfig);
        if (resultdata.getCode() != 0) {
            return R.newError(resultdata.getMsg());
        }
        Object obj=  dbOperationHelper.queryForObject(apiconfig.getSqlContent());
        if(obj==null){
            return R.newError("未查询到信息");
        }else {
            return R.newOk(obj);
        }
    }

    /**
     * 通用保存类接口方法
     *
     * @return 返回查询结果
     */
    private R<Object> operateApi(ApiConfigVO apiconfig, JSONObject param, HttpServletRequest req) throws Exception {
        if (apiconfig == null) {
            return R.newError("Api地址错误！");
        }
//        CurrentUserInfoVO currentUserInfo = CurrentHelp.Ins.GetUser();
//        LoginUser currentUserInfo = tokenService.getLoginUser(ServletUtils.getRequest());
        //如果微信提交的，要判断一下状态
        if(param.get("wxflag")==null){
            param.put("wxflag","0");
        }
        if("1".equals(param.getString("wxflag"))&&"SaveDataInfo".equalsIgnoreCase(apiconfig.getMethodCode()))
        {
            String fid = param.getString("$tableNewId")==null?"":param.getString("$tableNewId");
            if(!"".equals(fid))
            {
                String fromTableName = apiconfig.getCallMethodCode();
                fromTableName = fromTableName.replaceAll(".SaveDataInfo","");
                String sql = "select auditflag from "+fromTableName+" where  fid = '"+fid+"' limit 1";
                List<String> auditList = jdbcTemplate.queryForList(sql,String.class);
                if(auditList.size()>0){
                    String auditflag = auditList.get(0);
                    if("审核中".equals(auditflag)||"待确认".equals(auditflag))
                    {
                        return R.newError("数据审核中，不允许再次提交！");
                    }else if("审核通过".equals(auditflag))
                    {
                        return R.newError("数据已审核通过，不需要再次提交！");
                    }

                }
            }
        }
        ClientUser user = new ClientUser();
        param.put("$user", user);
        DBOperationHelper dbOperationHelper = new DBOperationHelper(jdbcTemplate, param);
        R<String> resultdata = bizformdata(req, param, dbOperationHelper, apiconfig);
        if (resultdata.getCode() != 0) {
            return R.newError(resultdata.getMsg());
        }
        dbOperationHelper.ExecuteOptSql(apiconfig.getSqlContent());
        //插入日志
        if("1".equals(param.getString("wxflag"))&&"SaveDataInfo".equalsIgnoreCase(apiconfig.getMethodCode()))
        {

            String fid = param.getString("$tableNewId")==null?"":param.getString("$tableNewId");
            if(!"".equals(fid)) {
                String fromTableName = apiconfig.getCallMethodCode();
                QuestionAuditProcess auditProcess = new QuestionAuditProcess();
                auditProcess.setAuditby("-1");
                auditProcess.setAuditbyname(" ");
                auditProcess.setAuditflag("");
                auditProcess.setAuditremark("学生提交数据");
                auditProcess.setCreateby("-1");
                auditProcess.setCreatebyname("学生");
                auditProcess.setCreatedate(new Date());
                auditProcess.setTablename(fromTableName);
                auditProcess.setFid(Long.valueOf(fid));
                auditProcessService.insertQuestionAuditProcess(auditProcess);
            }
        }
        return R.newOk();
    }

    /**
     * 验证参数
     *
     * @param
     * @return
     */
    private R<String> bizformdata(HttpServletRequest req, JSONObject param, DBOperationHelper dbOperationHelper, ApiConfigVO apiconfig) throws Exception {
        R<String> result = new R<String>();
        if (StringUtils.isNotBlank(apiconfig.getParamConfig())) {
            List<ParamBizFromVO> paramlist = JSONUtil.jsonToObjList(ParamBizFromVO.class, apiconfig.getParamConfig());
            JSONObject requestparam = new JSONObject();
            JSONObject headparam = new JSONObject();
            for (ParamBizFromVO item : paramlist) {
                if(item.getChildren()==null){

                }else if (item.getProperty().equals("body")) {
                    result = dbOperationHelper.bizformdata(item.getChildren());
                    if (result.getCode() != 0) {
                        return result;
                    }
                } else if (item.getProperty().equals("query")) {
                    for (ParamBizFromVO childrenitem : item.getChildren()) {
                        result = ValidateUtils.ValidateParam(childrenitem, req.getParameter(childrenitem.getProperty()));
                        if (result.getCode() != 0) {
                            return result;
                        } else {
                            requestparam.put(childrenitem.getProperty(), req.getParameter(childrenitem.getProperty()));
                        }
                    }

                } else if (item.getProperty().equals("header")) {
                    for (ParamBizFromVO childrenitem : item.getChildren()) {
                        if (childrenitem.getRequired()) {
                            result = ValidateUtils.ValidateParam(childrenitem, req.getHeader(childrenitem.getProperty()));
                            if (result.getCode() != 0) {
                                return result;
                            } else {
                                headparam.put(childrenitem.getProperty(), req.getHeader(childrenitem.getProperty()));
                            }
                        }
                    }
                }
            }
            param.put("$query", requestparam);
            param.put("$header", headparam);
            dbOperationHelper.put("$query", requestparam);
            dbOperationHelper.put("$header", headparam);
        }
        return result.newOk();
    }

    public R<List<ApplyUserVO>> queryApplyUserList(String tableName,String cardId,String examClass)
    {
        String sql = "select fid,"+cardId+","+examClass+" from "+tableName+" where  auditflag = '待确认' limit 10";
        List<Map<String,Object>> auditList = jdbcTemplate.queryForList(sql);
        List<ApplyUserVO> resultList = new ArrayList<ApplyUserVO>();
        for(Map<String,Object> itsm:auditList)
        {
            ApplyUserVO vo = new ApplyUserVO();
            vo.setFid(String.valueOf(itsm.get("fid")));
            vo.setCardId(String.valueOf(itsm.get(cardId)));
            vo.setExamClass(String.valueOf(itsm.get(examClass)));
            resultList.add(vo);
        }
        return R.newOk(resultList);
    }
    public R<List<AuditReportVo>> getAuditReport(String fromTableName) {
        String sql = "select auditflag as auditStatus,count(1) as auditStatusCount from " + fromTableName + " group by auditflag";
        List<Map<String, Object>> auditList = jdbcTemplate.queryForList(sql);
        List<AuditReportVo> auditReportVoList = new ArrayList<>();
        for (Map<String, Object> itsm : auditList){
            AuditReportVo vo = new AuditReportVo();
            vo.setAuditStatus(String.valueOf(itsm.get("auditStatus")));
            vo.setAuditStatusCount(String.valueOf(itsm.get("auditStatusCount")));
            auditReportVoList.add(vo);
        }
        return R.newOk(auditReportVoList);
    }
}
